-- @owner: @kyeleze
-- @date: 2022-10-24
-- @testpoint: count函数测试

--step1:建表建索引,插入数据;expect:成功
drop table if exists t_ustore_aggfunc_0021;
create table  t_ustore_aggfunc_0021(
 c_id integer,
 c_first varchar(20),
 c_middle character(2),
 c_zip char(9),
 c_d_id integer,
 c_street_1 character varying(20),
 c_city character varying(20),
 c_w_id integer,
 c_street_2 character varying(20)
 ) with (storage_type=ustore);

create index i_ustore_aggfunc_0021_01 on t_ustore_aggfunc_0021(c_id,c_d_id,c_w_id);
create index i_ustore_aggfunc_0021_02 on t_ustore_aggfunc_0021(c_first);
create index i_ustore_aggfunc_0021_03 on t_ustore_aggfunc_0021(c_city);
insert into t_ustore_aggfunc_0021

--step2:查询表 expect:成功
select c_id,c_first,c_middle,c_zip,c_d_id,c_street_1,c_city,c_w_id,c_street_2
from t_ustore_aggfunc_0021
where c_d_id>=2and c_w_id<5 and c_id>4 and c_id<=15 or c_id = 17 and c_w_id < 11 or c_id=19 and c_w_id < 11 or c_id=23 and c_w_id<11;

--step3:创建序列,开启匿名块 expect:成功
drop sequence if exists s_ustore_aggfunc_0021_01;
create sequence s_ustore_aggfunc_0021_01 start with 100 maxvalue 800 increment by 1 cycle;
declare
x number;
begin
x := 0;
loop
  insert into t_ustore_aggfunc_0021 values (null,'uuuuufjilodfj','oe',null,1890,'naniluyale',null,s_ustore_aggfunc_0021_01.nextval,null);
  x := x + 1;
  exit when x > 700;
end loop;
end;
/

declare
x number;
y number;
begin
x := 100;
y := 500;
loop
  y := 500;
  loop
    insert into t_ustore_aggfunc_0021 values (x,'iudhisfo',null,'guang',y,'tangyan','iofopdj21jjd',6,'gaoxin');
    y := y + 1;
    exit when y > 800;
  end loop;
  x := x + 1;
  exit when x > 800;
end loop;
end;
/

--step4:创建序列,开启匿名块 expect:成功
drop sequence if exists s_ustore_aggfunc_0021_02;
create sequence s_ustore_aggfunc_0021_02 start with 1 maxvalue 500 increment by 1 cycle;
declare
x number;
begin
x := 0;
loop
  insert into t_ustore_aggfunc_0021 values (s_ustore_aggfunc_0021_02.nextval,'safewereti','oe',null,null,'sosidelali','olkjfldsjfp',89765,null);
  x := x + 1;
  exit when x > 499;
end loop;
end;
/

--step5:count函数测试 expect:成功
select count(*) from t_ustore_aggfunc_0021;

--step6:清理环境 expect:成功
drop sequence if exists s_ustore_aggfunc_0021_01;
drop sequence if exists s_ustore_aggfunc_0021_02;
drop table if exists t_ustore_aggfunc_0021;
